package occ.service
{
  import flash.data.*;
  import flash.events.*;
  
  import occ.model.CustomerVO;
  
  import mx.collections.ArrayCollection;
  
  import occ.utils.*;
  
  public class Customer extends EventDispatcher
  {
    public static const SEARCH_RESULT:String="SearchResultEvent";
    public static const GET_CUSTOMER_RESULT:String="GetCustomerResultEvent";
    public static const SAVE_CUSTOMER_RESULT:String="SavedCustomerresultEvent";
    public static const NULL_CUSTOMER_ID:int=-1;
    
    private var sqlData:ArrayCollection;
		private var stmt:SQLStatement;
		private var sql:String;
		
		public var cvo:CustomerVO;
		public var searchResult:ArrayCollection;
		public var lastCustId:int;
		
    public function getAllCustomers():void
    {
      sql="select cust_id, first_name, last_name, address, city, state, zip, email, phone "+
          " from customer where active_flag ='Y' order by 3";
      stmt = new SQLStatement();
			stmt.sqlConnection = DatabaseManager.getConnection();
			
			stmt.addEventListener( SQLErrorEvent.ERROR, onSQLError );
			stmt.addEventListener( SQLEvent.RESULT, onSearchResult );
			stmt.text = sql;
			stmt.execute();
    }
    public function searchCustomer(s:String):void
    {
      sql="select cust_id, first_name, last_name, address, city, state, zip, email, phone "+
          " from customer where upper(last_name) like upper('" + s + "%') and active_flag='Y' order by 3";
      stmt = new SQLStatement();
			stmt.sqlConnection = DatabaseManager.getConnection();
			
			stmt.addEventListener( SQLErrorEvent.ERROR, onSQLError );
			stmt.addEventListener( SQLEvent.RESULT, onSearchResult );
			stmt.text = sql;
			stmt.execute();
    }
    
    private function onSearchResult(event:SQLEvent):void
    {
      var result:SQLResult = stmt.getResult();
			 searchResult= new ArrayCollection();
			 sqlData = new ArrayCollection( result.data );
			 for(var i:int=0;i<sqlData.length;i++){
			   var _cvo: CustomerVO = new CustomerVO();
			   _cvo.custId = (sqlData[i] as Object).cust_id;
			   _cvo.firstName = (sqlData[i] as Object).first_name;
			   _cvo.lastName = (sqlData[i] as Object).last_name;
         _cvo.fullName  = _cvo.firstName + " " + _cvo.lastName;
			   _cvo.address = (sqlData[i] as Object).address;
			   _cvo.city  = (sqlData[i] as Object).city;
			   _cvo.state = (sqlData[i] as Object).state;
			   _cvo.zip = (sqlData[i] as Object).zip;
			   _cvo.email = (sqlData[i] as Object).email;
			   _cvo.phone = (sqlData[i] as Object).phone;
			   searchResult.addItem(_cvo);
			   
			 }
			 //trace(" the cnt: " + (sqlData[0] as Object).name);
			 dispatchEvent(new Event(SEARCH_RESULT));
			 
    }
    
    public function getCustomer(i:int):void
    {
       sql="select cust_id, first_name, last_name, address, city, state, zip, email, phone "+
          " from customer where cust_id = " + i;
      stmt = new SQLStatement();
			stmt.sqlConnection = DatabaseManager.getConnection();
			
			stmt.addEventListener( SQLErrorEvent.ERROR, onSQLError );
			stmt.addEventListener( SQLEvent.RESULT, onGetCustomerResult );
			stmt.text = sql;
			stmt.execute();
    }
    
    private function onGetCustomerResult(event:SQLEvent):void
    {
      var result:SQLResult = stmt.getResult();
			 cvo = new CustomerVO;
			 sqlData = new ArrayCollection( result.data );
			 for(var i:int=0;i<sqlData.length;i++){
			   cvo.custId = (sqlData[i] as Object).cust_id;
			   cvo.firstName = (sqlData[i] as Object).first_name;
			   cvo.lastName = (sqlData[i] as Object).last_name;
			   cvo.address = (sqlData[i] as Object).address;
			   cvo.city  = (sqlData[i] as Object).city;
			   cvo.state = (sqlData[i] as Object).state;
			   cvo.zip = (sqlData[i] as Object).zip;
			   cvo.email = (sqlData[i] as Object).email;
			   cvo.phone = (sqlData[i] as Object).phone;
			 }
			 //trace(" the cnt: " + (sqlData[0] as Object).name);
			 dispatchEvent(new Event(GET_CUSTOMER_RESULT));
    }
    
    public function saveCustomer(cvo:CustomerVO):void
    {
      lastCustId = cvo.custId;
      
      if(cvo.custId == NULL_CUSTOMER_ID){
        trace("We should be inserting")
        sql ="insert into customer "+
            " (first_name, last_name, address, city, state, zip, phone, email) "+
            " values "+
            " ('" + cvo.firstName + "', '" + cvo.lastName + "', '" + cvo.address + "', '" + cvo.city + "', '" + cvo.state 
            + "', '" + cvo.zip + "', '" + cvo.phone + "', '" + cvo.email + "')";
        
      }else{
       sql="update customer"+
        " set first_name = '" + cvo.firstName + "', last_name = '" +cvo.lastName + "', address='" + cvo.address + "', "+ 
        " city='" + cvo.city + "', state='" + cvo.state + "', zip='" + cvo.zip + "', phone='" + cvo.phone + "', email='" + cvo.email + "' "+
        " where cust_id = " + cvo.custId;
      }
      trace(sql);
      stmt = new SQLStatement();
			stmt.sqlConnection = DatabaseManager.getConnection();
			
			stmt.addEventListener( SQLErrorEvent.ERROR, onSQLError );
			stmt.addEventListener( SQLEvent.RESULT, onSaveCustomerResult );
			stmt.text = sql;
			stmt.execute();
    }
    
    private function onSaveCustomerResult(event:SQLEvent):void
    {
      sql="select last_insert_rowid() as result";
      stmt = new SQLStatement();
			stmt.sqlConnection = DatabaseManager.getConnection();
			
			stmt.addEventListener( SQLErrorEvent.ERROR, onSQLError );
			stmt.addEventListener( SQLEvent.RESULT, onReportSaveResult );
			stmt.text = sql;
			stmt.execute();
    }
    
    private function onReportSaveResult(event:SQLEvent):void
    {
      trace("onReportSaveResult");
       if(lastCustId == NULL_CUSTOMER_ID){
         var result:SQLResult = stmt.getResult();
			   sqlData = new ArrayCollection( result.data );
			   for(var i:int=0;i<sqlData.length;i++){
  			   lastCustId = (sqlData[i] as Object).result
  			 }
       }
       dispatchEvent(new Event(SAVE_CUSTOMER_RESULT));
    }
      
    
     
         
      
			 
    
    private function onSQLError( event:SQLErrorEvent ):void
		{
			 var errorString:String = "SQL Error: " + event.error.errorID + "nDetails: " + event.error.details + "nDetail Arguments: " +event.error.detailArguments;
 			throw new Error(errorString);
		}

  }
}